package com.irdstudio.apicenter.gateway.core.jdbc;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.util.Assert;

import com.irdstudio.apicenter.gateway.core.annotation.Column;
import com.irdstudio.apicenter.gateway.core.annotation.Id;
import com.irdstudio.apicenter.gateway.core.annotation.Table;
import com.irdstudio.apicenter.gateway.core.dm.AppBaseInfo;
import com.irdstudio.apicenter.gateway.core.dm.MaccsBean;

/**
 * 移动服务端数据库工具类（JDBC）
 * 
 * 
 * <p>
 * <li>
 * 提供公共的面向对象的增删改查（需要使用注解,表名、字段名、主键支持注解解析）{@link Id} ,{@link Table} ,
 * {@link Column}
 * 
 * <li>
 * 提供子类轻量封装的通过SQL语句完成数据库操作. （自定义查询，等其他灵活查询,需要编写DAO类 继承该类
 * <p>
 * 
 * 
 * @author ZhangHao
 * @version 1.0
 * @date 2015-06-04
 *
 */
public class MaccsBaseDAO extends JdbcDaoSupport implements MaccsDAO {

	/**
	 * 新增一个bean 对象
	 * 
	 * @param bean
	 * @return
	 */
	public int insertBean(MaccsBean bean) {

		Assert.notNull(bean, "bean is null.");

		BeanSQLTemp sqlTemp = null;
		try {
			sqlTemp = SQLUtil.getInsertSQL(bean);
			Assert.notEmpty(sqlTemp.getParams(), " params is empty.");

		} catch (Exception e) {
			throw new MaccsDBException("insert error!", e);
		}

		return insert(sqlTemp.getSql(), sqlTemp.getParams());
	}

	/**
	 * 执行新增的SQL语句
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int insert(String sql, Object... params) {
		try {
			return this.update(sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			throw new MaccsDBException("insert error!", e);
		}
	}

	/**
	 * 新增一个bean 对象集合
	 * 
	 * @param beans
	 * @return
	 */
	// TODO 待完成
	public int[] insert(List<MaccsBean> beans) {
		return null;
	}

	/**
	 * 删除一个 bean 对象（根据主键）
	 * <p>
	 * 主键必须使用{@link Id}注解标识
	 * 
	 * @param bean
	 * @return
	 */
	public int deleteBean(MaccsBean bean) {

		BeanSQLTemp bTemp = null;
		try {
			Assert.notNull(bean, "bean is null.");
			bTemp = SQLUtil.getDeleteSQL(bean);
			Assert.notEmpty(bTemp.getParams(), " params is empty.");
		} catch (Exception e) {
			throw new MaccsDBException("delete error!", e);
		}

		return delete(bTemp.getSql(), bTemp.getParams());
	}

	/**
	 * 执行删除的SQL语句
	 * 
	 * @param conn
	 * @param sql
	 * @param params
	 * @return
	 */
	public int delete(String sql, Object... params) {
		try {
			return this.update(sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			throw new MaccsDBException("insert error!", e);
		}
	}

	/**
	 * 修改一个bean对象(根据主键)
	 * <p>
	 * 主键必须使用{@link Id}注解标识
	 * 
	 * @param obj
	 * @return
	 */
	public int update(MaccsBean bean) {

		
		BeanSQLTemp sqlTemp = null;
		try {
			Assert.notNull(bean, "bean is null.");
			sqlTemp = SQLUtil.getUpdateSQL(bean);
			Assert.notEmpty(sqlTemp.getParams(), " params is empty.");
		} catch (Exception e) {
			throw new MaccsDBException("update error!", e);
		}

		return update(sqlTemp.getSql(), sqlTemp.getParams());
	}

	/**
	 * 执行一个SQL语句（新增、修改、删除）
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int update(String sql, Object... params) {
		return this.getJdbcTemplate().update(sql, params);
	}

	/**
	 * 
	 * 查询一个bean对象（根据主键）
	 * 
	 * @param beanClass
	 * @param keyPropName
	 * @param idPropertyValue
	 * @return
	 */
	public <T> T findBean(Class<T> beanClass, Object[] idPropertyValues) {

		RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(beanClass);

		String sql = null;
		Object bean = null;
		try {

			Assert.notNull(beanClass, "beanClass is null.");
			Assert.notEmpty(idPropertyValues, "idPropertyValues is empty.");
			bean = beanClass.newInstance();

			sql = SQLUtil.getSelectSQL(bean);
			return this.getJdbcTemplate().queryForObject(sql, rowMapper,
					idPropertyValues);

		} catch (EmptyResultDataAccessException e) {
			// 如果查询不到记录,则返回空
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			throw new MaccsDBException("find error!", e);
		}
	}

	/**
	 * 查询一个bean 集合
	 * 
	 * @param sql
	 * @param returnType
	 * @param paramValue
	 * @return
	 */
	public <T> List<T> findBeanList(Class<T> returnType) {
		String sql = null;
		try {
			sql = SQLUtil.getSelectAllSQL(returnType.newInstance());
		} catch (Exception e) {
			throw new MaccsDBException("findBeanList error!", e);
		}
		return this.findBeanList(sql, returnType);
	}

	/**
	 * 查询一个bean 集合
	 * 
	 * @param sql
	 * @param returnType
	 * @param paramValue
	 * @return
	 */
	public <T> List<T> findBeanList(String sql, Class<T> returnType,
			Object... params) {
		RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(returnType);
		return this.getJdbcTemplate().query(sql, rowMapper, params);
	}

	/**
	 * 查询一个Map 集合
	 * 
	 * @param tableName
	 * @return
	 */
	public List<Map<String, Object>> findMapList(String tableName) {
		String sql;
		try {
			sql = SQLUtil.getSelectAllSQL(tableName);
		} catch (Exception e) {
			throw new MaccsDBException("findMapList error!", e);
		}
		return this.getJdbcTemplate().queryForList(sql);
	}

	/**
	 * 查询一个Map 集合
	 * 
	 * @param sql
	 * @param paramValue
	 * @return
	 */
	public List<Map<String, Object>> findMapList(String sql,
			Object [] params) {
		return this.getJdbcTemplate().queryForList(sql, params);
	}

	/**
	 * 查询一个 Int 值
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public int findForInt(String sql, Object... params) {
		Number number = (Number) queryForObject(sql, Integer.class, params);
		return number != null ? number.intValue() : 0;
	}

	/**
	 * 查询一个 Long 值
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public long findForLong(String sql, Object... params) {
		Number number = (Number) queryForObject(sql, Long.class, params);
		return number != null ? number.longValue() : 0;
	}

	/**
	 * 查询一个 Long 值
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public String findForString(String sql, Object... params) {
		Object obj = queryForObject(sql, Long.class, params);
		return obj != null ? obj.toString() : null;
	}

	/**
	 * 查询一个制定类型的值
	 * 
	 * @param sql
	 * @param requiredType
	 * @param args
	 * @return
	 */
	public <T> T queryForObject(String sql, Class<T> requiredType,
			Object... args) {
		try {
			return this.getJdbcTemplate().queryForObject(sql, requiredType,
					args);
		} catch (EmptyResultDataAccessException e) {
			// 如果查询不到记录,则返回空
			return null;
		}		
	}
	
	/**
	 * 查询并返回字符串
	 * @param sql
	 * @param objects
	 * @param class1
	 * @return
	 */
	public String queryForObject(String sql, Object[] objects,
			Class<String> class1) {
		try {
			return this.getJdbcTemplate().queryForObject(sql, objects, class1);
		} catch (EmptyResultDataAccessException e) {
			// 如果查询不到记录,则返回空
			return "";
		}
	}
	
	/**
	 * 返回MAP
	 * @param string
	 * @param objects
	 * @return
	 */
	public Map<String, Object> queryForMap(String sql, Object[] args) {
		try {
			return this.getJdbcTemplate().queryForMap(sql, args);
		} catch (EmptyResultDataAccessException e) {
			// 如果查询不到记录,则返回空
			return null;
		}
	}

	/**
	 * 
	 * SQL 工具 包括SQL语句的拼装等方法
	 * 
	 * 
	 * @author ZhangHao
	 * @version 1.0
	 * @date 2015-06-04
	 *
	 */

	static class SQLUtil {

		/**
		 * 根据属性名称获取数据库字段名
		 * 
		 * @param prop
		 * @return
		 */
		public static String getTrueFieldName(String prop, Object bean) {
			StringBuilder columnName = new StringBuilder();

			if (bean != null) { // 优先使用注解定义的字段名
				Field field = FieldUtils.getDeclaredField(bean.getClass(),
						prop, true);
				if (field != null && field.getAnnotation(Column.class) != null)
					return field.getAnnotation(Column.class).name();

			}

			for (int i = 0; i < prop.length(); i++) {
				if (Character.isUpperCase(prop.charAt(i))) {
					if (i > 0) {
						columnName.append("_");
					}
				}
				columnName.append(prop.charAt(i));
			}
			return columnName.toString();
		}

		/**
		 * 根据bean获取数据库表名
		 * <p>
		 * 支持注解
		 * 
		 * @param tableClass
		 * @return
		 */
		public static String getTrueTableName(Object bean) {

			Table table = bean.getClass().getAnnotation(Table.class); // 优先使用注解中定义的表名
			if (table != null && StringUtils.isNotBlank(table.name()))
				return table.name();

			String name = bean.getClass().getSimpleName();
			name = (new StringBuilder(String.valueOf(Character.toLowerCase(name
					.charAt(0)))).append(name.substring(1))).toString();

			return getTrueFieldName(name, null);
		}

		/**
		 * 根据bean获取新增SQL语句
		 * 
		 * @param obj
		 * @return
		 * @throws Exception
		 */
		public static BeanSQLTemp getInsertSQL(Object obj) throws Exception {
			String tableName = SQLUtil.getTrueTableName(obj);
			StringBuilder sql = new StringBuilder();
			StringBuilder body1 = new StringBuilder();
			StringBuilder body2 = new StringBuilder();
			sql.append("insert into  ").append(tableName).append("(");
			Map<String, String> map = BeanUtils.describe(obj);

			List<Object> params = new ArrayList<Object>(map.size());
			for (Entry<String, String> entry : map.entrySet()) {
				String prop = entry.getKey();
				if ("class".equals(prop))
					continue;
				body1.append(SQLUtil.getTrueFieldName(prop, obj)).append(",");
				body2.append("?,");

				params.add(entry.getValue());
			}
			body1.deleteCharAt(body1.length() - 1);
			body2.deleteCharAt(body2.length() - 1);
			sql.append(body1).append(") values (").append(body2).append(")");

			return new BeanSQLTemp(sql.toString(), params.toArray());
		}

		/**
		 * 
		 * 根据bean 生成使用主键删除语句
		 * 
		 * @param obj
		 * @return
		 * @throws Exception
		 */
		public static BeanSQLTemp getDeleteSQL(Object obj) throws Exception {

			String tableName = SQLUtil.getTrueTableName(obj);
			StringBuilder sql = new StringBuilder();
			sql.append("delete from ").append(tableName).append(" where ");
			String fieldNames[] = SQLUtil.getBeanId(obj);
			List<Object> params = new ArrayList<Object>();
			for (int i = 0; i < fieldNames.length; i++) {
				sql.append(SQLUtil.getTrueFieldName(fieldNames[i], obj))
						.append("=?");
				params.add(BeanUtils.getProperty(obj, fieldNames[i]));
				sql.append((i + 1) == fieldNames.length ? " " : " and ");
			}

			return new BeanSQLTemp(sql.toString(), params.toArray());
		}

		/**
		 * 获取bean 对象的ID名称(根据注解{@link Id}
		 * 
		 * @param obj
		 * @return
		 */
		public static String[] getBeanId(Object obj) {

			Field[] fields = obj.getClass().getDeclaredFields();
			List<String> list = new ArrayList<String>(2);
			for (Field field : fields) {
				if (field.getAnnotation(Id.class) != null) {
					list.add(field.getName());
				}
			}

			return list.toArray(new String[0]);
		}

		/**
		 * 获取bean 对象的更新语句
		 * 
		 * @param conn
		 * @param obj
		 * @param keyPropName
		 * @return
		 * @throws Exception
		 */
		public static BeanSQLTemp getUpdateSQL(Object obj) throws Exception {

			String tableName = getTrueTableName(obj);
			StringBuilder sql = new StringBuilder();
			sql.append("update ").append(tableName).append(" SET ");
			Map<String, String> map = BeanUtils.describe(obj);
			String[] id = SQLUtil.getBeanId(obj);
			List<Object> params = new ArrayList<Object>();
			for (Entry<String, String> entry : map.entrySet()) {
				String prop = entry.getKey();
				if (ArrayUtils.contains(id, prop) || "class".equals(prop))
					continue;

				sql.append(getTrueFieldName(prop, obj)).append("=?")
						.append(",");
				params.add(entry.getValue());
			}

			sql.deleteCharAt(sql.length() - 1);
			sql.append(" where ");

			int i = 1;
			for (String idd : id) {
				sql.append(getTrueFieldName(idd, obj)).append("=?");
				sql.append(i == id.length ? " " : " and ");
				i++;
				params.add(BeanUtils.getProperty(obj, idd));
			}

			return new BeanSQLTemp(sql.toString(), params.toArray());
		}

		/**
		 * 获取bean对象的查询语句(根据主键）
		 * 
		 * @param keyPropName
		 * @param obj
		 * @return
		 */
		public static String getSelectSQL(Object obj) {

			StringBuilder sql = new StringBuilder();
			sql.append(" select * from   ").append(getTrueTableName(obj))
					.append(" where ");

			String[] id = SQLUtil.getBeanId(obj);
			for (int i = 0; i < id.length; i++) {
				sql.append(getTrueFieldName(id[i], obj)).append(" =? ");
				sql.append((i + 1) == id.length ? " " : " and  ");
			}

			return sql.toString();
		}

		/**
		 * 获取查询集合（不带条件)的语句
		 * 
		 * @param obj
		 * @return
		 */
		public static String getSelectAllSQL(Object obj) {

			String tableName = null;
			if (obj instanceof MaccsBean)
				tableName = getTrueTableName(obj);

			if (obj instanceof String)
				tableName = (String) obj;
			
			Assert.notNull(obj,"getSelectAllSQL error");

			return new StringBuilder("select * from ").append(tableName)
					.toString();
		}

		/**
		 * 获取bean 对象的属性值
		 * 
		 * @param obj
		 * @return
		 * @throws Exception
		 */
		public static List<String> getParams(Object obj, String... pnames)
				throws Exception {
			Map<String, String> map = BeanUtils.describe(obj);
			List<String> paramList = new ArrayList<String>(map.size());
			for (String prop : map.keySet()) {
				if (prop.equals("class")) {
					continue;
				}
				paramList.add(BeanUtils.getProperty(obj, prop));
			}
			return paramList;
		}
	}

	public static void main(String[] args) {

		AppBaseInfo appBaseInfo = new AppBaseInfo();
		appBaseInfo.setAppId("1111");
		appBaseInfo.setAppName("22222");
		try {
			BeanSQLTemp beanSQLTemp = SQLUtil.getInsertSQL(appBaseInfo);
			System.err.println(beanSQLTemp.getSql());
			System.err.println(ArrayUtils.toString(beanSQLTemp.getParams()));

			appBaseInfo.setAppId("1111");
			appBaseInfo.setAppName("22222");
			beanSQLTemp = SQLUtil.getDeleteSQL(appBaseInfo);
			System.err.println(beanSQLTemp.getSql());
			System.err.println(ArrayUtils.toString(beanSQLTemp.getParams()));

			beanSQLTemp = SQLUtil.getUpdateSQL(appBaseInfo);
			System.err.println(beanSQLTemp.getSql());
			System.err.println(ArrayUtils.toString(beanSQLTemp.getParams()));

			System.err.println(SQLUtil.getSelectSQL(appBaseInfo));

			System.err.println(SQLUtil.getSelectAllSQL(appBaseInfo));

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
